# -*- coding: utf8 -*-

from project.sqlite3_db.table import Table

class TableFile( Table ):

    _table_name = "file"

    def create( self ):
        cur = self.getCursor()

        # Create table
        cur.execute(
            "CREATE TABLE IF NOT EXISTS file ("+
            "id INTEGER PRIMARY KEY, "+
            "parent_id INTEGER, "+
            "hash_id INTEGER, "+
            "device INTEGER, "+
            "size INTEGER, "+
            "mtime INTEGER, "+
            "mime TEXT, "+
            "name TEXT)"
        )
        cur.execute(
            "CREATE INDEX IF NOT EXISTS hash ON file (hash_id)"
        )
        cur.close()
        return


    def insert_file( self, parent_id, device, mime, size, mtime, name ):
        cur = self.getCursor()

        cur.execute(
            "INSERT INTO file("+
            "parent_id, device, mime, size, mtime, name"+
            ") VALUES ("+
            ":parent_id, :device, :mime, :size, :mtime, :name"+
            ")", {
                "parent_id":parent_id,
                "device":device,
                "mime":mime, "size":size, "mtime":mtime,
                "name":name
            }
        )
        self.getConnection().commit()
        file_id = cur.lastrowid
        cur.close()
        return file_id


    def link_hash( self, file_id, hash_id ):
        cur = self.getCursor()

        cur.execute("UPDATE file SET hash_id=:hash WHERE id=:file", {
            "hash": hash_id,
            "file": file_id
        })

        self.getConnection().commit()

        rc = cur.rowcount
        cur.close()
        return rc


    def get_file_hash_id( self, file_id ):
        cur = self.getCursor()
        cur.execute("SELECT hash_id FROM file WHERE id=:id", {"id": file_id})
        item = cur.fetchone()
        cur.close()
        if item:
            return item["hash_id"]
        return False


    def get_nonuniq_hash_ids(self):
        cur = self.getCursor()

        hash_ids = ()

        cur.execute("SELECT hash_id FROM file WHERE hash_id IS NOT NULL GROUP BY hash_id HAVING COUNT(id)>1")
        for item in cur.fetchall():
            hash_ids += (str(item["hash_id"]),)

        cur.close()
        return hash_ids


    def get_file2hash_by_hash_ids( self, hash_ids ):
        cur = self.getCursor()

        file_hash = []
        if len(hash_ids):
            cur.execute("SELECT id,hash_id FROM file WHERE hash_id IN (" + ",".join(hash_ids) + ")" )
            file_hash = cur.fetchall()

        cur.close()
        return file_hash


    def get_by_ids( self, file_ids ):
        cur = self.getCursor()

        files = []
        if len(file_ids):
            cur.execute("SELECT * FROM file WHERE id IN (" + ",".join(file_ids) + ")" )
            files = cur.fetchall()

        cur.close()
        return files


    def get_file( self, file_id ):
        cur = self.getCursor()

        cur.execute("SELECT * FROM file WHERE id=:id", {"id":file_id} )
        item = cur.fetchone()

        cur.close()
        return item


    def remove_file( self, file_id ):
        cur = self.getCursor()
        cur.execute("DELETE FROM file WHERE id=:id",
                {"id":file_id}
        )
        self.getConnection().commit()
        rc = cur.rowcount
        cur.close()
        return rc

    pass
